﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;

namespace SisLegal.Model
{
    public class FiscalFolder
    {
        public int ID { get; set; }
        public string NroFiscalFolder { get; set; }
        public int Year { get; set; }
        public string Fiscalia { get; set; }
        public string FiscalCargo { get; set; }
        public DateTime FechaHechos { get; set; }
        public DateTime FechaNoticicacionSeal { get; set; }
        public DateTime FechaInicio { get; set; }
        public decimal MontoPretension { get; set; }
        public int ParticipationSeal { get; set; }
        public int Article { get; set; }
        public int Money { get; set; }
        public int TypeProcess { get; set; }
        public int ContingencyRate { get; set; }
        public string PoliciaCargo { get; set; }
        public string Denunciante { get; set; }
        public string Denunciado { get; set; }
        public string Agraviado { get; set; }
        public string DocumentoInicial { get; set; }
        public string Sumilla { get; set; }
        public string Observaciones { get; set; }
        //---------------------------------------------
        public int AssignedPerson { get; set; }
        public int Lawyer { get; set; }
        public string OrdenServicio { get; set; }
        public string NroRecibo { get; set; }
        public string TerminosReferencia { get; set; }
        public string AbogadoSeal { get; set; }
        public string PracticanteSeal { get; set; }
        public int PhysicalLocationFiles { get; set; }

        public FiscalFolder()
        {
            this.ID = 0;
            this.NroFiscalFolder = "";
            this.Year = 0;
            this.Fiscalia = "";
            this.FiscalCargo = "";
            this.FechaHechos = new DateTime();
            this.FechaNoticicacionSeal = new DateTime();
            this.FechaInicio = new DateTime();
            this.MontoPretension = 0;
            this.ParticipationSeal = 0;
            this.Article = 0;
            this.Money = 0;
            this.TypeProcess = 0;
            this.ContingencyRate = 0;
            this.PoliciaCargo = "";
            this.Denunciante = "";
            this.Denunciado = "";
            this.Agraviado = "";
            this.DocumentoInicial = "";
            this.Sumilla = "";
            this.Observaciones = "";
            //---------------------------------------------
            this.AssignedPerson = 0;
            this.Lawyer = 0;
            this.OrdenServicio = "";
            this.NroRecibo = "";
            this.TerminosReferencia = "";
            this.AbogadoSeal = "";
            this.PracticanteSeal = "";
            this.PhysicalLocationFiles = 0;
        }
    }
    public class FiscalFolderManager
    {
        public List<FiscalFolder> GetAll()
        {
            List<FiscalFolder> result = new List<FiscalFolder>();
            string sqlQuery = String.Format("SELECT * FROM FiscalFolder ORDER BY ID");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            FiscalFolder process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new FiscalFolder();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroFiscalFolder = dataReader["NroFiscalFolder"].ToString();
                    process.Year = Convert.ToInt32(dataReader["Year"]);
                    process.Fiscalia = dataReader["Fiscalia"].ToString();
                    process.FiscalCargo = dataReader["FiscalCargo"].ToString();
                    process.FechaHechos = new DateTime();
                    process.FechaNoticicacionSeal = new DateTime();
                    process.FechaInicio = new DateTime();
                    process.MontoPretension = Convert.ToDecimal(dataReader["MontoPretension"]);
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.Article = Convert.ToInt32(dataReader["Article"]);
                    process.Money = Convert.ToInt32(dataReader["Money"]);
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    process.ContingencyRate = Convert.ToInt32(dataReader["ContingencyRate"]);
                    process.PoliciaCargo = dataReader["PoliciaCargo"].ToString();
                    process.Denunciante = dataReader["Denunciante"].ToString();
                    process.Denunciado = dataReader["Denunciado"].ToString();
                    process.Agraviado = dataReader["Agraviado"].ToString();
                    process.DocumentoInicial = dataReader["DocumentoInicial"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    //---------------------------------------------
                    process.AssignedPerson = Convert.ToInt32(dataReader["AssignedPerson"]);
                    process.Lawyer = Convert.ToInt32(dataReader["Lawyer"]);
                    process.OrdenServicio = dataReader["OrdenServicio"].ToString();
                    process.NroRecibo = dataReader["NroRecibo"].ToString();
                    process.TerminosReferencia = dataReader["TerminosReferencia"].ToString();
                    process.AbogadoSeal = dataReader["AbogadoSeal"].ToString();
                    process.PracticanteSeal = dataReader["PracticanteSeal"].ToString();
                    process.PhysicalLocationFiles = Convert.ToInt32(dataReader["PhysicalLocationFiles"]);
                    result.Add(process);
                }
            }
            return result;
        }
        public Int32 Count()
        {
            List<FiscalFolder> result = new List<FiscalFolder>();
            string sqlQuery = String.Format("  SELECT COUNT(ID) AS total FROM FiscalFolder");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            command.CommandType = CommandType.Text;
            Int32 countStart = (Int32)command.ExecuteScalar();
            return countStart;
        }

        public List<FiscalFolder> Paginate(Int32 pag)
        {
            List<FiscalFolder> result = new List<FiscalFolder>();
            string sqlQuery = String.Format("SELECT * FROM (" +
                "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, * FROM FiscalFolder " +
                ")as tbl " +
                "WHERE NUMBER BETWEEN((" + Convert.ToString(pag) + "-1) * (10 +1)) AND (" + Convert.ToString(pag) + " * 10) ORDER BY ID");

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            FiscalFolder process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new FiscalFolder();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroFiscalFolder = dataReader["NroFiscalFolder"].ToString();
                    process.Year = Convert.ToInt32(dataReader["Year"]);
                    process.Fiscalia = dataReader["Fiscalia"].ToString();
                    process.FiscalCargo = dataReader["FiscalCargo"].ToString();
                    process.FechaHechos = new DateTime();
                    process.FechaNoticicacionSeal = new DateTime();
                    process.FechaInicio = new DateTime();
                    process.MontoPretension = Convert.ToDecimal(dataReader["MontoPretension"]);
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.Article = Convert.ToInt32(dataReader["Article"]);
                    process.Money = Convert.ToInt32(dataReader["Money"]);
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    process.ContingencyRate = Convert.ToInt32(dataReader["ContingencyRate"]);
                    process.PoliciaCargo = dataReader["PoliciaCargo"].ToString();
                    process.Denunciante = dataReader["Denunciante"].ToString();
                    process.Denunciado = dataReader["Denunciado"].ToString();
                    process.Agraviado = dataReader["Agraviado"].ToString();
                    process.DocumentoInicial = dataReader["DocumentoInicial"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    //---------------------------------------------
                    process.AssignedPerson = Convert.ToInt32(dataReader["AssignedPerson"]);
                    process.Lawyer = Convert.ToInt32(dataReader["Lawyer"]);
                    process.OrdenServicio = dataReader["OrdenServicio"].ToString();
                    process.NroRecibo = dataReader["NroRecibo"].ToString();
                    process.TerminosReferencia = dataReader["TerminosReferencia"].ToString();
                    process.AbogadoSeal = dataReader["AbogadoSeal"].ToString();
                    process.PracticanteSeal = dataReader["PracticanteSeal"].ToString();
                    process.PhysicalLocationFiles = Convert.ToInt32(dataReader["PhysicalLocationFiles"]);
                    result.Add(process);
                }
            }
            return result;
        }

        public int Save(FiscalFolder obj)
        {
            string createQuery = String.Format("Insert into FiscalFolder (NroFiscalFolder,"+
                    "Year, Fiscalia, FiscalCargo, FechaHechos, FechaNoticicacionSeal, FechaInicio, MontoPretension, ParticipationSeal,"+
                    "Article, Money, TypeProcess, ContingencyRate, PoliciaCargo, Denunciante, Denunciado, Agraviado," +
                    "DocumentoInicial, Sumilla, Observaciones, AssignedPerson, Lawyer, OrdenServicio, NroRecibo,"+
                    "TerminosReferencia, AbogadoSeal, PracticanteSeal, PhysicalLocationFiles) Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}');"
                + "Select @@Identity", obj.NroFiscalFolder, obj.Year, obj.Fiscalia,
                    obj.FiscalCargo, obj.FechaHechos, obj.FechaNoticicacionSeal, obj.FechaInicio,
                    obj.MontoPretension, obj.ParticipationSeal, obj.Article, obj.Money,
                    obj.TypeProcess, obj.ContingencyRate, obj.PoliciaCargo, obj.Denunciante,
                    obj.Denunciado, obj.Agraviado, obj.DocumentoInicial, obj.Sumilla,
                    obj.Observaciones, obj.AssignedPerson, obj.Lawyer, obj.OrdenServicio,
                    obj.NroRecibo, obj.TerminosReferencia, obj.AbogadoSeal, obj.PracticanteSeal,
                    obj.PhysicalLocationFiles);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;

            command = new SqlCommand(createQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
            }
            catch (Exception ex)
            {

            }

            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }

        /*public int Update(FiscalFolder obj)
        {
            string updateQuery = String.Format("Update FiscalFolder SET Name='{0}' Where ID = {1};",
               obj.Name, obj.ID);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;
            command = new SqlCommand(updateQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
                else
                {
                    saveID = obj.ID;
                }
            }
            catch (Exception ex)
            {

            }
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }*/

        public FiscalFolder GetById(int articleId)
        {
            FiscalFolder process = new FiscalFolder();
            string sqlQuery = String.Format("select * from FiscalFolder where ID={0}", articleId);
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    //result.ID = Convert.ToInt32(dataReader["ID"]);
                    //process = new FiscalFolder();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroFiscalFolder = dataReader["NroFiscalFolder"].ToString();
                    process.Year = Convert.ToInt32(dataReader["Year"]);
                    process.Fiscalia = dataReader["Fiscalia"].ToString();
                    process.FiscalCargo = dataReader["FiscalCargo"].ToString();
                    process.FechaHechos = new DateTime();
                    process.FechaNoticicacionSeal = new DateTime();
                    process.FechaInicio = new DateTime();
                    process.MontoPretension = Convert.ToDecimal(dataReader["MontoPretension"]);
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.Article = Convert.ToInt32(dataReader["Article"]);
                    process.Money = Convert.ToInt32(dataReader["Money"]);
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    process.ContingencyRate = Convert.ToInt32(dataReader["ContingencyRate"]);
                    process.PoliciaCargo = dataReader["PoliciaCargo"].ToString();
                    process.Denunciante = dataReader["Denunciante"].ToString();
                    process.Denunciado = dataReader["Denunciado"].ToString();
                    process.Agraviado = dataReader["Agraviado"].ToString();
                    process.DocumentoInicial = dataReader["DocumentoInicial"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    //---------------------------------------------
                    process.AssignedPerson = Convert.ToInt32(dataReader["AssignedPerson"]);
                    process.Lawyer = Convert.ToInt32(dataReader["Lawyer"]);
                    process.OrdenServicio = dataReader["OrdenServicio"].ToString();
                    process.NroRecibo = dataReader["NroRecibo"].ToString();
                    process.TerminosReferencia = dataReader["TerminosReferencia"].ToString();
                    process.AbogadoSeal = dataReader["AbogadoSeal"].ToString();
                    process.PracticanteSeal = dataReader["PracticanteSeal"].ToString();
                    process.PhysicalLocationFiles = Convert.ToInt32(dataReader["PhysicalLocationFiles"]);
                    
                }
            }

            return process;
        }

        public bool Delete(int articleId)
        {
            bool result = false;
            string sqlQuery = String.Format("delete from FiscalFolder where ID = {0}", articleId);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            int rowsDeletedCount = command.ExecuteNonQuery();
            if (rowsDeletedCount != 0)
                result = true;
            command.Dispose();
            connection.Close();
            connection.Dispose();
            return result;
        }
    }
}